﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SYH.Config;
using System.Data.SqlClient;

namespace SYH.Public.DAL
{
    public class Public_MessagesSenders
    {
        #region 根据id查找发件
        /// <summary>
        /// 根据id查找发件
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.Public_MessagesSenders GetSenderById(string id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select a.Id,a.MailID,a.SenderId,b.Contents,b.CreateTime 
            from Public_WebMessageSenders a inner join Public_WebMessage b on a.MailId = b.Id 
            where a.IsDeleted = 0 and b.Id = @Id");
            SqlParameter parameter = new SqlParameter("@Id", SqlDbType.Int, 4);
            parameter.Value = id;
            SqlDataReader reader = Common.SqlHelper.ExecuteReader(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameter);
            SYH.Public.Model.Public_MessagesSenders model = new Model.Public_MessagesSenders();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    model.Id = reader.GetInt32(reader.GetOrdinal("Id"));
                    model.MailId = reader.GetInt32(reader.GetOrdinal("MailID"));
                    model.SenderId = reader.GetInt32(reader.GetOrdinal("SenderId"));
                    model.Contents = reader.GetString(reader.GetOrdinal("Contents"));
                    model.CreateTime = reader.GetDateTime(reader.GetOrdinal("CreateTime"));
                }
                reader.Close();
                return model;
            }
            reader.Close();
            return null;
        }
        #endregion


        #region 消息回复
        public int ReplyMessage(int mailId, int receiverId, int senderId, string contents)
        {
            SqlParameter outParam = new SqlParameter("@newMailId", SqlDbType.Int);
            outParam.Direction = ParameterDirection.Output;
            Common.SqlHelper.ExecuteNonQuery(
                WebConfig.SqlConnString,
                CommandType.StoredProcedure,
                "Public_WebMessage_AddReply",
                new SqlParameter("@MailId", mailId),
                new SqlParameter("@SenderId", senderId),
                new SqlParameter("@ReceiverId", receiverId),
                new SqlParameter("@Contents", contents),
                outParam);
            return Common.TypeParse.ObjectToInt(outParam.Value, 0);
        }
        #endregion


        #region  发件箱
        /// <summary>
        /// 发件箱
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <param name="sqlWhere"></param>
        /// <returns></returns>
        public DataTable GetList(int pageSize, int pageIndex, int UserId, out int pageCount, out int recordCount, string sqlWhere)
        {
            string fields = @"PM.Id,PM.Title,PM.CreateTime,PS.TypeId,PS.Id as SendId,PS.MailID ";
            string tableName = @"qhw_shop.dbo.Public_WebMessage as PM 
                                 inner join qhw_shop.dbo.Public_WebMessageSenders as PS
                                 on pM.Id=PS.MailID";
            string strCondition = sqlWhere;
            strCondition += " and PS.SenderId=" + UserId + " and PS.IsDeleted=0";
            return Common.PublicSqlData.SimplePager(WebConfig.SqlConnString
                , tableName
                , fields
                , pageSize
                , pageIndex
                , "Id"
                , true
                , strCondition
                , out pageCount
                , out recordCount
                , 0).Tables[0];
        }
        #endregion

        #region  根据mailid查找收件人
        /// <summary>
        /// 根据mailid查找收件人
        /// </summary>
        /// <param name="mailId"></param>
        /// <returns></returns>
        public DataSet SearchReceivers(int mailId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT User_CompanyInfo.CompanyName, User_SimplyInfo.Name FROM Public_WebMessageReceiver INNER JOIN User_CompanyInfo ON Public_WebMessageReceiver.ReceiverId = User_CompanyInfo.UserId INNER JOIN User_SimplyInfo ON User_CompanyInfo.UserId = User_SimplyInfo.Id and MailId=@MailId");
            SqlParameter parameter = new SqlParameter("@MailId", SqlDbType.Int, 4);
            parameter.Value = mailId;
            DataSet ds = Common.SqlHelper.ExecuteDataset(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameter);
            return ds;
        }
        #endregion

        #region  删除
        public int Delete(string idList)
        {
            string[] idArray = idList.Trim(',').Split(',');
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Public_WebMessageSenders set IsDeleted = 1 where ");
            strSql.Append(" Id in (" + idList + ")");
            SqlParameter parameter = new SqlParameter("@IdList", SqlDbType.VarChar, 200);
            parameter.Value = idList;
            int rows = Common.SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameter);

            return rows;
        }
        #endregion

        #region 清空
        /// <summary>
        /// 删除全部发件箱里的东西（就是那个清空按钮）
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int DeleteAllSendMsg(int userId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Public_WebMessageSenders set IsDeleted = 1 where  SenderId = @SenderId ");
            SqlParameter parameter = new SqlParameter("@SenderId", SqlDbType.Int, 4);
            parameter.Value = userId;
            int rows = Common.SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameter);
            return rows;
        }
        #endregion

        #region 根据mailids查找发件人们
        /// <summary>
        /// 根据mailid查找发件人们
        /// </summary>
        /// <param name="mailIds"></param>
        /// <returns></returns>
        public DataSet SearchSenders(string mailIds)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(" SELECT uc.CompanyName, us.Name,pws.MailId FROM Public_WebMessageSenders pws INNER JOIN User_CompanyInfo uc ON pws.SenderId = uc.UserId INNER JOIN User_SimplyInfo us ON uc.UserId = us.Id and MailId in({0}) ", mailIds);
            DataSet ds = Common.SqlHelper.ExecuteDataset(WebConfig.SqlConnString, CommandType.Text, strSql.ToString());
            return ds;
        }
        #endregion
    }
}
